package jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

//DAO Data Access Object
public class StudentDao {
	static Connection con = null;

	static Statement stm = null;

	static String sql = null;
	static ResultSet rs;

	public static void main(String[] args) throws Exception {
//		tes();
		 Student s = new Student("hean",1,23,new Date());
		Student s1 = new Student("myi'th", 3, 23, new Date());
		save(s1);
		save(s);
		// delete(1);
		// update(s);
		// System.out.println(findById(1));
		// System.out.println(findById(2));
//		List<Student> list = new ArrayList<Student>();
//		list = findAll();
//		for (Student s : list) {
//			System.out.println(s);
//		}
		
//		deleteByIds(new int[]{1,2,3});
//		List<Student> list = new ArrayList<Student>();
//		list = findByAgeRange(23,24);
//		for (Student s : list) {
//			System.out.println(s);
//		}
	}

	public static void deleteByIds(int[] ids) throws SQLException, ClassNotFoundException {
		if(ids==null||ids.length==0){
			return;
		}
//		for (int i : ids) {
//			delete(i);//连接打开关闭次数太多
//		}
		String s= "";
		for (int i = 0; i < ids.length; i++) {
			if(i>0)
				s+=",";
			s+=ids[i];
		}
		sql="delete from hh_stu where id in ("+s+")";
	}

	public static List<Student> findByAgeRange(int minAge, int maxAge)
			throws Exception {
		con = DBUtil.open();
		stm=con.createStatement();
		sql="select * from hh_stu where age between "+minAge+" and "+maxAge;
		ResultSet rs = stm.executeQuery(sql);
		List<Student> list = new ArrayList<Student>();
		while(rs.next()){
			Student s = new Student();
			s.setAge(rs.getInt("age"));
			s.setBirthday(rs.getTimestamp("birthday"));
			s.setId(rs.getInt("id"));
			s.setName(rs.getString("name"));
			list.add(s);
		}
		return list;
	}

	public static void save(Student stu) throws Exception {

		try {// finally要与try一起用，此处try不是用来捕获异常
			con = DBUtil.open();
			stm = con.createStatement();
			sql = "insert into hh_stu (id,name,age) values(" + stu.getId()
					+ ",'" + stu.getName().replaceAll("'", "''") + "',"
					+ stu.getAge() + ")";
			stm.executeUpdate(sql);
		} finally {
			DBUtil.close(con, stm, null);// 非常重要，数据库连接数有限，不用即断，让出资源
		}
	}

	public static void delete(int id) throws Exception {
		try {// finally要与try一起用，此处try不是用来捕获异常
			con = DBUtil.open();
			stm = con.createStatement();
			sql = "delete from hh_stu where id =" + id + "";
			stm.executeUpdate(sql);
		} finally {
			DBUtil.close(con, stm, null);// 非常重要，数据库连接数有限，不用即断，让出资源
		}
	}

	public static void update(Student stu) throws Exception {
		try {// finally要与try一起用，此处try不是用来捕获异常
			con = DBUtil.open();
			stm = con.createStatement();
			sql = "update hh_stu set name='" + stu.getName() + "',age="
					+ stu.getAge() + " where id = " + stu.getId();
			stm.executeUpdate(sql);
		} finally {
			DBUtil.close(con, stm, null);// 非常重要，数据库连接数有限，不用即断，让出资源
		}
	}

		public static Student findByAge(int age) throws Exception{
			try {
				con=DBUtil.open();
				stm=con.createStatement();
				sql="select * from hh_stu where age= "+age;
				con.setAutoCommit(false);
				rs = stm.executeQuery(sql);
				con.commit();
				if (!rs.next()) {
					return null;
				}
				Student s = new Student();
				s.setAge(age);
				s.setBirthday(rs.getTimestamp("birthday"));
				s.setId(rs.getInt("id"));
				s.setName(rs.getString("name"));
				return s;
			} finally {
				DBUtil.close(con,stm,rs);
			}
		}
	
	public static Student findById(int id) throws Exception {
		try {// finally要与try一起用，此处try不是用来捕获异常
			con = DBUtil.open();
			stm = con.createStatement();
			sql = "select * from hh_stu where id = " + id;
			ResultSet rs = stm.executeQuery(sql);
			if (!rs.next()) {
				return null;
			}
			Student s = new Student();
			s.setAge(rs.getInt("age"));
			s.setName(rs.getString("name"));
			s.setId(id);
			s.setBirthday(rs.getTimestamp("birthday"));
			return s;
		} finally {
			DBUtil.close(con, stm, null);// 非常重要，数据库连接数有限，不用即断，让出资源
		}
	}

	public static List<Student> findAll() throws Exception {
		List<Student> list = new ArrayList<Student>();
		try {// finally要与try一起用，此处try不是用来捕获异常
			con = DBUtil.open();
			stm = con.createStatement();
			sql = "select * from hh_stu ";
			ResultSet rs = stm.executeQuery(sql);
			while (rs.next()) {
				Student s = new Student();
				s.setAge(rs.getInt("age"));
				s.setName(rs.getString("name"));
				s.setId(rs.getInt("id"));
				s.setBirthday(rs.getTimestamp("birthday"));
				list.add(s);
			}
			return list;
		} finally {
			DBUtil.close(con, stm, null);// 非常重要，数据库连接数有限，不用即断，让出资源
		}
	}

	public static void tes() throws Exception {
		Date d = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String s = sdf.format(d);
		System.out.println(s);
	}

}
